import pymysql


def getDataOverview():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    types = ['公共汽电车运营数', '轨道交通配属车辆数', '公共汽电车运营线路总长度', '轨道交通运营里程',
             '公共汽电车客运量', '轨道交通客运量']
    data = []
    for i in types:
        sql = "SELECT * from environment where type='{}' and year=2022".format(i)
        cur.execute(sql)
        datas = cur.fetchall()
        data += [float(i['number']) for i in datas]
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def getPermanentPopulation():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from population where type='年末常住人口数' and city='广东省' ORDER BY year"
    cur.execute(sql)
    datas = cur.fetchall()
    years = [i['year'] for i in datas]
    numbers = [float(i['number']) for i in datas]
    data = {'years': years, 'numbers': numbers}
    print(data)
    cur.close()
    conn.close()
    return data


def genderRatioOfPopulation():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from population where type='男性人口比重' and city='广东省' and year=2022 "
    cur.execute(sql)
    datas = cur.fetchall()
    number = [float(i['number']) for i in datas][0]
    data = [{'name': '男性占比', 'value': number}, {'name': '女性占比', 'value': 100 - number}]
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def getGDP():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from economic where type='地区生产总值' and city='广东省'"
    cur.execute(sql)
    datas = cur.fetchall()
    number = [float(i['number']) for i in datas]
    years = [i['year'] for i in datas]
    data = {'years': years, 'numbers': number}
    print(data)
    cur.close()
    conn.close()
    return data


def averageHousePrice():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    types = ['商品房平均销售价格', '住宅商品房平均销售价格', '商业营业用房平均销售价格', '其他商品房平均销售价格']
    data = {'name': types, 'year': [2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022], 'price': []}
    for i in types:
        sql = "SELECT * from realestate where type='{}' and city='广东省' ".format(i)
        cur.execute(sql)
        datas = cur.fetchall()
        data['price'].append({
            'name': i,
            'type': 'bar',
            'barWidth': 12,
            'barGap': 0,
            'data': [float(i['number']) for i in datas]})

    print(data)
    cur.close()
    conn.close()
    return data


def getCityAQI():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from environment where type='AQI达标率' ORDER BY number desc LIMIT 5"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['city'] for i in datas]
    number = [float(i['number']) for i in datas]
    data = {'city': city, 'number': []}
    for i in range(len(city)):
        data['number'].append({'name': city[i], 'value': number[i]})
    print(data)
    cur.close()
    conn.close()
    return data


def perCapitaConsumptionExpenditure():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from economic where type='全体居民人均消费支出' and city='广东省' ORDER BY year"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['year'] for i in datas]
    number = [round(float(i['number']), 1) for i in datas]
    data = {'city': city, 'number': number}
    print(data)
    cur.close()
    conn.close()
    return data


def getMap():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = ("SELECT * from population where type='年末常住人口数' and year=2022 and city!='广东省' "
           "and city!='珠三角' and city!='山区' and city!='东翼' and city!='西翼'")
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['city'] for i in datas]
    number = [round(float(i['number']), 1) for i in datas]
    data = []
    for i in range(len(city)):
        data.append({'name': city[i] + '市', 'value': number[i]})
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    getMap()
